package priv.bruce.service;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import priv.bruce.entry.MovieInfo;
import priv.bruce.util.HandlerSet;
import priv.bruce.util.JDBCUtil;

public class MovieInfoService {

	public List<Map<String, String>> findById4Detail(int Id) throws Exception {
		String sql = "select m.*,AVG(score),(select count(*) "
				+ "from t_score where movie_id=?) as all_num,(select count(*) "
				+ "from t_score where movie_id=? and score=10) as ten_num,(select count(*) "
				+ "from t_score where movie_id=? and score=8) as eight_num,(select count(*) "
				+ "from t_score where movie_id=? and score=6) as six_num,(select count(*) "
				+ "from t_score where movie_id=? and score=4) as four_num,(select count(*) "
				+ "from t_score where movie_id=? and score=2) as two_num from "
				+ "t_movie as m join t_score as s on m.movie_id=s.movie_id where m.movie_id=?";
		
		List<Map<String, String>> list = null;
		try {
			list=JDBCUtil.query(sql, new HandlerSet<Map<String, String>>() {
				@Override
				public Map<String, String> handler(ResultSet rs) throws SQLException {
					Map<String, String> map = new HashMap<>();
					map.put("movie_id",rs.getString("movie_id"));
					map.put("movie_name",rs.getString("movie_name"));
					map.put("movie_director",rs.getString("movie_director"));
					map.put("movie_actor",rs.getString("movie_actor"));
					map.put("movie_writer",rs.getString("movie_writer"));
					map.put("movie_type",rs.getString("movie_type"));
					map.put("movie_region",rs.getString("movie_region"));
					map.put("movie_lang",rs.getString("movie_lang"));
					map.put("movie_date",rs.getString("movie_date"));
					map.put("movie_length",rs.getString("movie_length"));
					map.put("movie_alias",rs.getString("movie_alias"));
					map.put("movie_juqing",rs.getString("movie_juqing"));
					map.put("movie_cover",rs.getString("movie_cover"));
					map.put("jishu", rs.getString("jishu"));
					map.put("avg",rs.getString("AVG(score)"));
					map.put("all_num",rs.getString("all_num"));
					map.put("ten_num",rs.getString("ten_num"));
					map.put("eight_num",rs.getString("eight_num"));
					map.put("six_num",rs.getString("six_num"));
					map.put("four_num",rs.getString("four_num"));
					map.put("two_num",rs.getString("two_num"));
					return map;
				}
				
			},Id,Id,Id,Id,Id,Id,Id);
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	public List<MovieInfo> findById(int Id) throws Exception{
		String sql="select * from t_movie where movie_id=?";
		return JDBCUtil.query(sql,  new HandlerSet<MovieInfo>(){
			@Override
			public MovieInfo handler(ResultSet rs) throws SQLException {
				MovieInfo movieInfo = new MovieInfo();
				movieInfo.setMovie_id(rs.getInt("movie_id"));
				movieInfo.setMovie_name(rs.getString("movie_name"));
				movieInfo.setMovie_director(rs.getString("movie_director"));
				movieInfo.setMovie_writer(rs.getString("movie_writer"));
				movieInfo.setMovie_actor(rs.getString("movie_actor"));
				movieInfo.setMovie_type(rs.getString("movie_type"));
				movieInfo.setMovie_region(rs.getString("movie_region"));
				movieInfo.setMovie_lang(rs.getString("movie_lang"));
				movieInfo.setMovie_date(rs.getDate("movie_date"));
				movieInfo.setMovie_length(rs.getString("movie_length"));
				movieInfo.setMovie_alias(rs.getString("movie_alias"));
				movieInfo.setMovie_juqing(rs.getString("movie_juqing"));
				movieInfo.setMovie_cover(rs.getString("movie_cover"));
				movieInfo.setJishu(rs.getInt("jishu"));
				return movieInfo;
			}
		}, Id);
	}

	public List<MovieInfo> findMovie() throws Exception{
		String sql4movie="select * from t_movie where jishu=1 order by movie_date DESC";
		
		return JDBCUtil.query(sql4movie,  new HandlerSet<MovieInfo>(){
			@Override
			public MovieInfo handler(ResultSet rs) throws SQLException {
				MovieInfo movieInfo = new MovieInfo();
				movieInfo.setMovie_id(rs.getInt("movie_id"));
				movieInfo.setMovie_name(rs.getString("movie_name"));
				movieInfo.setMovie_director(rs.getString("movie_director"));
				movieInfo.setMovie_writer(rs.getString("movie_writer"));
				movieInfo.setMovie_actor(rs.getString("movie_actor"));
				movieInfo.setMovie_type(rs.getString("movie_type"));
				movieInfo.setMovie_region(rs.getString("movie_region"));
				movieInfo.setMovie_lang(rs.getString("movie_lang"));
				movieInfo.setMovie_date(rs.getDate("movie_date"));
				movieInfo.setMovie_length(rs.getString("movie_length"));
				movieInfo.setMovie_alias(rs.getString("movie_alias"));
				movieInfo.setMovie_juqing(rs.getString("movie_juqing"));
				movieInfo.setMovie_cover(rs.getString("movie_cover"));

				return movieInfo;
			}
		});
	}

	public List<MovieInfo> findTV() throws Exception{
		String sql4TV="select * from t_movie where jishu>1 order by movie_date DESC";
		
		return JDBCUtil.query(sql4TV,  new HandlerSet<MovieInfo>(){
			@Override
			public MovieInfo handler(ResultSet rs) throws SQLException {
				MovieInfo movieInfo = new MovieInfo();
				movieInfo.setMovie_id(rs.getInt("movie_id"));
				movieInfo.setMovie_name(rs.getString("movie_name"));
				movieInfo.setMovie_director(rs.getString("movie_director"));
				movieInfo.setMovie_writer(rs.getString("movie_writer"));
				movieInfo.setMovie_actor(rs.getString("movie_actor"));
				movieInfo.setMovie_type(rs.getString("movie_type"));
				movieInfo.setMovie_region(rs.getString("movie_region"));
				movieInfo.setMovie_lang(rs.getString("movie_lang"));
				movieInfo.setMovie_date(rs.getDate("movie_date"));
				movieInfo.setMovie_length(rs.getString("movie_length"));
				movieInfo.setMovie_alias(rs.getString("movie_alias"));
				movieInfo.setMovie_juqing(rs.getString("movie_juqing"));
				movieInfo.setMovie_cover(rs.getString("movie_cover"));
				return movieInfo;
			}
		});
	}


	public List<MovieInfo> findByName(String movieName) throws Exception {
		String sql = "select * from t_movie where movie_name like CONCAT('%',?,'%') ";
		return JDBCUtil.query(sql, new HandlerSet<MovieInfo>() {

			@Override
			public MovieInfo handler(ResultSet rs) throws SQLException {
				MovieInfo movieInfo = new MovieInfo();
				movieInfo.setMovie_id(rs.getInt("movie_id"));
				movieInfo.setMovie_name(rs.getString("movie_name"));
				movieInfo.setMovie_director(rs.getString("movie_director"));
				movieInfo.setMovie_writer(rs.getString("movie_writer"));
				movieInfo.setMovie_actor(rs.getString("movie_actor"));
				movieInfo.setMovie_type(rs.getString("movie_type"));
				movieInfo.setMovie_region(rs.getString("movie_region"));
				movieInfo.setMovie_lang(rs.getString("movie_lang"));
				movieInfo.setMovie_date(rs.getDate("movie_date"));
				movieInfo.setMovie_length(rs.getString("movie_length"));
				movieInfo.setMovie_alias(rs.getString("movie_alias"));
				movieInfo.setMovie_juqing(rs.getString("movie_juqing"));
				movieInfo.setMovie_cover(rs.getString("movie_cover"));
				movieInfo.setJishu(rs.getInt("jishu"));
				return movieInfo;
			}
		}, movieName);
	}
	
	public List<MovieInfo> findByName4admin(String movieName) throws Exception {
		String sql = "select * from t_movie where movie_name = ?";
		return JDBCUtil.query(sql, new HandlerSet<MovieInfo>() {

			@Override
			public MovieInfo handler(ResultSet rs) throws SQLException {
				MovieInfo movieInfo = new MovieInfo();
				movieInfo.setMovie_id(rs.getInt("movie_id"));
				movieInfo.setMovie_name(rs.getString("movie_name"));
				movieInfo.setMovie_director(rs.getString("movie_director"));
				movieInfo.setMovie_writer(rs.getString("movie_writer"));
				movieInfo.setMovie_actor(rs.getString("movie_actor"));
				movieInfo.setMovie_type(rs.getString("movie_type"));
				movieInfo.setMovie_region(rs.getString("movie_region"));
				movieInfo.setMovie_lang(rs.getString("movie_lang"));
				movieInfo.setMovie_date(rs.getDate("movie_date"));
				movieInfo.setMovie_length(rs.getString("movie_length"));
				movieInfo.setMovie_alias(rs.getString("movie_alias"));
				movieInfo.setMovie_juqing(rs.getString("movie_juqing"));
				movieInfo.setMovie_cover(rs.getString("movie_cover"));
				movieInfo.setJishu(rs.getInt("jishu"));
				return movieInfo;
			}
		}, movieName);
	}
	
	public int addMovie(String movieName,String director,String writer,String actor,String type,String region,
			String language,Date date,String length,String alias,String juqing,String cover,int jishu) throws Exception {
		String sql="insert into t_movie(movie_name,movie_director,movie_writer,movie_actor,movie_type,movie_region,"
				+ "movie_lang,movie_date,movie_length,movie_alias,movie_juqing,movie_cover,jishu)"
				+ "values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
		return JDBCUtil.update(sql, movieName,director,writer,actor,type,region,language,
				date,length,alias,juqing,cover,jishu);
	}
	
	public int modifyMovie(String field , Object value , int id) throws Exception {
		String sql = "UPDATE t_movie SET " + field + " = ?  WHERE movie_id = ?";
		return JDBCUtil.update(sql , value , id);
	}
	
	public int deleteById(int id) throws Exception {
		String sql = "DELETE FROM t_movie WHERE movie_id = ?";
		return JDBCUtil.update(sql , id);
	}
	
	public List<MovieInfo> showKouBei() throws Exception{
		String sql = "SELECT * FROM t_movie as m join t_score as s "
				+ " on m.movie_id = s.movie_id GROUP BY " + 
				" s.movie_id ORDER BY AVG(score) DESC,movie_date DESC";
		
		return JDBCUtil.query(sql, new HandlerSet<MovieInfo>() {

			@Override
			public MovieInfo handler(ResultSet rs) throws SQLException {
				MovieInfo movieInfo = new MovieInfo();
				movieInfo.setMovie_id(rs.getInt("movie_id"));
				movieInfo.setMovie_name(rs.getString("movie_name"));
				movieInfo.setMovie_director(rs.getString("movie_director"));
				movieInfo.setMovie_writer(rs.getString("movie_writer"));
				movieInfo.setMovie_actor(rs.getString("movie_actor"));
				movieInfo.setMovie_type(rs.getString("movie_type"));
				movieInfo.setMovie_region(rs.getString("movie_region"));
				movieInfo.setMovie_lang(rs.getString("movie_lang"));
				movieInfo.setMovie_date(rs.getDate("movie_date"));
				movieInfo.setMovie_length(rs.getString("movie_length"));
				movieInfo.setMovie_alias(rs.getString("movie_alias"));
				movieInfo.setMovie_juqing(rs.getString("movie_juqing"));
				movieInfo.setMovie_cover(rs.getString("movie_cover"));
				movieInfo.setJishu(rs.getInt("jishu"));
				return movieInfo;
			}
		});
	}
}
